import pandas as pd
import numpy as np
month = '202411'
# pre_year = '202312'
# pre_month = '202410'
pre_year_int = int(int(month[0:4]) - 1)
pre_year = str(pre_year_int) + '12'
if month[4:6] != '01':
    pre_month = str(int(int(month) - 1))
else:
    pre_month = str(pre_year_int) + '12'
month_int = int(month[4:6])
# 税率
tax_rate = 0.25
# month_int = 11
# Sheet3
# 补充一个参数
xlsx_name = 'D:/repos/sicost/' + month + '/实现利税,利润总额.xlsx'
df_1 = pd.read_excel(xlsx_name)
df_1 = df_1[['SERIAL_NUM', 'COMPANY_NAME', 'CURR_YEAR_VALUE_2', 'CURR_MONTH_VALUE_2']]
df_1 = df_1.reset_index(drop=False)
df_1.rename(columns={'index': 'INDEX'}, inplace=True)
df_1_1 = df_1[df_1['COMPANY_NAME'] == '非钢铁企业合计']
df_1_1 = df_1_1.reset_index(drop=True)

success = df_1_1.empty is False
if success is False:
    print('空的不需要处理')
else:
    index_tmp = df_1_1.loc[0]['INDEX']
    df_1_2 = df_1[df_1['INDEX'] < index_tmp]
    df_1_2 = df_1_2.reset_index(drop=True)
    df_1 = df_1_2
df_1.drop(['INDEX'], axis=1, inplace=True)
df_1['SERIAL_NUM_STR'] = df_1['SERIAL_NUM'].astype(str)
def __cal_strincludenum(x):
    if '0' in x.SERIAL_NUM_STR:
        rst = 1
    elif '1' in x.SERIAL_NUM_STR:
        rst = 1
    elif '2' in x.SERIAL_NUM_STR:
        rst = 1
    elif '3' in x.SERIAL_NUM_STR:
        rst = 1
    elif '4' in x.SERIAL_NUM_STR:
        rst = 1
    elif '5' in x.SERIAL_NUM_STR:
        rst = 1
    elif '6' in x.SERIAL_NUM_STR:
        rst = 1
    elif '7' in x.SERIAL_NUM_STR:
        rst = 1
    elif '8' in x.SERIAL_NUM_STR:
        rst = 1
    elif '9' in x.SERIAL_NUM_STR:
        rst = 1
    else:
        rst = 0
    return rst


df_1['include_type'] = df_1.apply(lambda x: __cal_strincludenum(x), axis=1)
df_1 = df_1[(df_1['include_type'] == 1) | (df_1['COMPANY_NAME'] == '钢铁企业合计')]
df_1 = df_1.reset_index(drop=True)

df_1.drop(['SERIAL_NUM_STR'], axis=1, inplace=True)
df_1.drop(['include_type'], axis=1, inplace=True)
df_1.rename(columns={'CURR_YEAR_VALUE_2': '利润总额'}, inplace=True)
df_1.rename(columns={'CURR_MONTH_VALUE_2': '利润总额_本月'}, inplace=True)

df_1['利润总额'] = df_1['利润总额'] / 10000
df_1['利润总额_本月'] = df_1['利润总额_本月'] / 10000

xlsx_name = 'D:/repos/sicost/' + month + '/研发费用,当期计提折旧额.xlsx'
df_2 = pd.read_excel(xlsx_name)
df_2 = df_2[['COMPANY_NAME', 'CURR_YEAR_VALUE_2', 'CURR_MONTH_VALUE_2']]
df_2.rename(columns={'CURR_YEAR_VALUE_2': '折旧'}, inplace=True)
df_2.rename(columns={'CURR_MONTH_VALUE_2': '折旧_本月'}, inplace=True)

df_2['折旧'] = df_2['折旧'] / 10000
df_2['折旧_本月'] = df_2['折旧_本月'] / 10000

xlsx_name = 'D:/repos/sicost/' + month + '/财务费用,利息支出.xlsx'
df_3 = pd.read_excel(xlsx_name)
df_3 = df_3[['COMPANY_NAME', 'CURR_YEAR_VALUE_2', 'CURR_MONTH_VALUE_2']]
df_3.rename(columns={'CURR_YEAR_VALUE_2': '利息支出'}, inplace=True)
df_3.rename(columns={'CURR_MONTH_VALUE_2': '利息支出_本月'}, inplace=True)

df_3['利息支出'] = df_3['利息支出'] / 10000
df_3['利息支出_本月'] = df_3['利息支出_本月'] / 10000

xlsx_name = 'D:/repos/sicost/' + month + '/吨钢利润,吨钢期间费用.xlsx'
df_4 = pd.read_excel(xlsx_name)
df_4 = df_4[['COMPANY_NAME', 'CURR_YEAR_VALUE_1', 'CURR_MONTH_VALUE_1']]
df_4.rename(columns={'CURR_YEAR_VALUE_1': '吨钢利润'}, inplace=True)
df_4.rename(columns={'CURR_MONTH_VALUE_1': '吨钢利润_本月'}, inplace=True)

v = ['COMPANY_NAME']
df_0 = pd.merge(df_1, df_2, on=v, how='left')
df_0 = pd.merge(df_0, df_3, on=v, how='left')
df_0 = pd.merge(df_0, df_4, on=v, how='left')

df_0 = df_0[df_0['COMPANY_NAME'] != '钢铁企业合计']
df_0 = df_0.reset_index(drop=True)
df_0['利润总额'] = df_0['利润总额'].fillna(0)
df_0['折旧'] = df_0['折旧'].fillna(0)
df_0['利息支出'] = df_0['利息支出'].fillna(0)
df_0['利润总额_本月'] = df_0['利润总额_本月'].fillna(0)
df_0['折旧_本月'] = df_0['折旧_本月'].fillna(0)
df_0['利息支出_本月'] = df_0['利息支出_本月'].fillna(0)

df_0['EBITDA'] = df_0['利润总额'] + df_0['折旧'] + df_0['利息支出']
# df_0['反算钢产量'] = df_0['利润总额'] / df_0['吨钢利润'] * 10000
def __cal_production(x):
    if x.吨钢利润 == 0:
        rst = 0
    else:
        rst = x.利润总额 / x.吨钢利润 * 10000
    return rst


df_0['反算钢产量'] = df_0.apply(lambda x: __cal_production(x), axis=1)
df_0['钢产量'] = df_0['反算钢产量']
# df_0['吨钢EBITDA'] = df_0['EBITDA'] / df_0['反算钢产量'] * 10000
def __cal_ebitda(x):
    if x.反算钢产量 == 0:
        rst = -10000
    else:
        rst = x.EBITDA / x.反算钢产量 * 10000
    return rst


df_0['吨钢EBITDA'] = df_0.apply(lambda x: __cal_ebitda(x), axis=1)
# df_0['吨钢利润+折旧'] = (df_0['利润总额'] + df_0['折旧']) / df_0['反算钢产量'] * 10000
def __cal_lirunzhejiu(x):
    if x.反算钢产量 == 0:
        rst = 0
    else:
        rst = (x.利润总额 + x.折旧) / x.反算钢产量 * 10000
    return rst


df_0['吨钢利润+折旧'] = df_0.apply(lambda x: __cal_lirunzhejiu(x), axis=1)
###########################
df_0['EBITDA_本月'] = df_0['利润总额_本月'] + df_0['折旧_本月'] + df_0['利息支出_本月']
# df_0['反算钢产量_本月'] = df_0['利润总额_本月'] / df_0['吨钢利润_本月'] * 10000
def __cal_production2(x):
    if x.吨钢利润_本月 == 0:
        rst = 0
    else:
        rst = x.利润总额_本月 / x.吨钢利润_本月 * 10000
    return rst


df_0['反算钢产量_本月'] = df_0.apply(lambda x: __cal_production2(x), axis=1)
# df_0['吨钢EBITDA_本月'] = df_0['EBITDA_本月'] / df_0['反算钢产量_本月'] * 10000
def __cal_ebitda2(x):
    if x.反算钢产量_本月 == 0:
        rst = -10000
    else:
        rst = x.EBITDA_本月 / x.反算钢产量_本月 * 10000
    return rst


df_0['吨钢EBITDA_本月'] = df_0.apply(lambda x: __cal_ebitda2(x), axis=1)
desired_order = ['SERIAL_NUM', 'COMPANY_NAME', '钢产量', '利润总额', '折旧', '利息支出', 'EBITDA', '吨钢EBITDA', '吨钢利润+折旧',
                 '利润总额_本月', '吨钢利润_本月', '反算钢产量_本月', '折旧_本月', '利息支出_本月', 'EBITDA_本月', '吨钢EBITDA_本月']
df_0 = df_0.reindex(columns=desired_order)

writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_吨钢EBITDA.xlsx')
df_0.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()


# df_out = pd.DataFrame(columns=['百分位', '分位值'])
df_out = pd.DataFrame(columns=['PR', 'PR_VALUE'])
dict_out = {}
for i in range(1, 19):
    # print((i+1)*5)
    percentiles = (i+1) * 5 / 100
    dict_out['PR'] = str(int((i+1) * 5)) + '分位'
    quantiles = df_0['吨钢EBITDA'].quantile(percentiles)
    dict_out['PR_VALUE'] = quantiles
    new_row = pd.Series(dict_out)
    df_out = df_out.append(new_row, ignore_index=True)
writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_吨钢EBITDA_PR.xlsx')
df_out.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
print('finish')
